package mysql

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"strconv"

	"fmt"
	"github.com/jmoiron/sqlx"
	"match-spider/util/prop"
	"math"
	"strings"
)

type MysqlDB interface {
	Insert(sql string, params ...interface{}) int64
	UpdateOrDelete(sql string, params ...interface{}) bool
	FindFirst(sql string, params ...interface{}) map[string]interface{}
	FindAll(sql string, params ...interface{}) []map[string]interface{}
	Count(sql string, params ...interface{}) int64
	Paginate(query, sqlExceptSelect string, page, size int64, params ...interface{}) map[string]interface{}
	Tx(
		callback func(tx *sql.Tx) bool,
		rollbackCallback func(),
		errCallback func(err error),
	)
	Exec(sql string, params ...interface{}) (map[string]int64, bool)
}
type mysqlDB struct {
	dbMap *sqlx.DB
}

func NewMysql(dbMap *sqlx.DB) MysqlDB {
	return &mysqlDB{
		dbMap: dbMap,
	}
}
func InitDb() (*sqlx.DB, error) {

	dbmap, err := sqlx.Open(prop.Get("db", "db.driver"), prop.Get("db", "db.username")+":"+prop.Get("db", "db.password")+"@"+prop.Get("db", "db.url"))

	if err != nil {
		return nil, err
	}
	dbmap.SetMaxOpenConns(20000)
	dbmap.SetMaxIdleConns(10000)
	dbmap.Ping()

	return dbmap, nil
}
func (mysql *mysqlDB) Insert(sql string, params ...interface{}) int64 {

	stmtIns, err := mysql.dbMap.Prepare(sql)

	if err != nil {
		panic(err.Error()) // proper error handling instead of panic in your app
	}
	defer stmtIns.Close() // Close the statement when we leave main() / the program terminates
	ret, err := stmtIns.Exec(params...)
	if err != nil {
		fmt.Println(err)
		return int64(0)
	}
	id, err := ret.LastInsertId()
	if err != nil {
		fmt.Println(err)
		return int64(0)
	}
	return id
}
func (mysql *mysqlDB) UpdateOrDelete(sql string, params ...interface{}) bool {

	stmtIns, err := mysql.dbMap.Prepare(sql)

	if err != nil {
		panic(err.Error()) // proper error handling instead of panic in your app
	}
	defer stmtIns.Close() // Close the statement when we leave main() / the program terminates
	ret, err := stmtIns.Exec(params...)
	if err != nil {
		return false
	}
	_, err = ret.RowsAffected()
	if err != nil {
		return false
	}

	return true
}
func (mysql *mysqlDB) Exec(sql string, params ...interface{}) (map[string]int64, bool) {

	stmtIns, err := mysql.dbMap.Prepare(sql)

	if err != nil {
		panic(err.Error()) // proper error handling instead of panic in your app
	}
	defer stmtIns.Close() // Close the statement when we leave main() / the program terminates
	ret, err := stmtIns.Exec(params...)

	if err != nil {
		return nil, false
	}
	data := make(map[string]int64)
	i1, err := ret.RowsAffected()
	if err != nil {
		return nil, false
	}
	data["rowsAffected"] = i1

	i2, err := ret.LastInsertId()
	if err != nil {
		return nil, false
	}
	data["lastInsertId"] = i2

	return data, true
}
func (mysql *mysqlDB) FindFirst(sql string, params ...interface{}) map[string]interface{} {

	sql = sql + " limit 1"
	rows, err := mysql.dbMap.Query(sql, params...)
	defer rows.Close()

	if err != nil {
		println(err.Error())
	}
	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))
	record := make(map[string]interface{})
	for i := range values {
		scanArgs[i] = &values[i]
	}
	var len = 0
	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		if err != nil {
			panic(err.Error()) // proper error handling instead of panic in your app
		}
		for i, col := range values {
			if col != nil {
				switch col.(type) {
				case []byte:
					record[columns[i]] = string(col.([]uint8))
				default:
					record[columns[i]] = col
				}
			}
		}
		len++
		break
	}
	if len > 0 {
		return record
	}
	return nil
}
func (mysql *mysqlDB) Count(sql string, params ...interface{}) int64 {

	sql = sql + " limit 1"
	rows, err := mysql.dbMap.Query(sql, params...)
	defer rows.Close()

	if err != nil {
		println(err.Error())
	}
	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([][]byte, len(columns))
	var count = "0"
	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)

		for _, col := range values {
			if col != nil {
				count = string(col)
				break
			}
		}
		break
	}
	num, _ := strconv.ParseInt(count, 10, 64)
	return num
}
func (mysql *mysqlDB) FindAll(sql string, params ...interface{}) []map[string]interface{} {

	rows, err := mysql.dbMap.Query(sql, params...)
	defer rows.Close()

	if err != nil {
		println(err.Error())
	}
	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([][]byte, len(columns))
	records := []map[string]interface{}{}
	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		record := make(map[string]interface{})
		for i, col := range values {
			if col != nil {
				record[columns[i]] = string(col)
			}
		}
		records = append(records, record)
	}
	return records
}

func (mysql *mysqlDB) Paginate(query, sqlExceptSelect string, page, size int64, params ...interface{}) map[string]interface{} {

	data := make(map[string]interface{})
	sqlExceptSelectCount := sqlExceptSelect
	ss := strings.Replace(sqlExceptSelect, " ", "", -1)
	if strings.Contains(ss, "orderby") {
		sqlExceptSelectCount = strings.Split(sqlExceptSelectCount, "order by")[0]
	}
	queryCount := "select count(*) as count " + sqlExceptSelectCount

	totalRows := mysql.Count(queryCount, params...)
	records := []map[string]string{}

	data["rows"] = int64(0)
	data["pages"] = int64(0)
	data["list"] = records

	if totalRows == 0 {

		return data
	}
	if page == 0 {
		page = 1
	}
	if size == 0 {
		size = 20
	}
	offset := (page - 1) * size

	sql := query + " " + sqlExceptSelect + " limit " + strconv.FormatInt(size, 10) + " offset " + strconv.FormatInt(offset, 10)

	rows, err := mysql.dbMap.Query(sql, params...)
	defer rows.Close()

	if err != nil {

		return data
	}
	columns, _ := rows.Columns()
	scanArgs := make([]interface{}, len(columns))
	values := make([][]byte, len(columns))

	for i := range values {
		scanArgs[i] = &values[i]
	}

	for rows.Next() {
		//将行数据保存到record字典
		err = rows.Scan(scanArgs...)
		record := make(map[string]string)
		for i, col := range values {
			if col != nil {
				record[columns[i]] = string(col)
			}
		}
		records = append(records, record)
	}
	totalPages := int64(math.Floor(float64(totalRows)/float64(size)) + 1)
	if totalRows%size == 0 {
		totalPages = totalRows / size
	}
	data["rows"] = totalRows
	data["pages"] = totalPages
	data["list"] = records
	return data
}
func (mysql *mysqlDB) Tx(
	callback func(tx *sql.Tx) bool,
	rollbackCallback func(),
	errCallback func(err error),
) {

	tx, err := mysql.dbMap.Begin()
	if err != nil {
		errCallback(err)
	}

	defer clearTransaction(tx, rollbackCallback, errCallback)

	if callback(tx) {
		tx.Commit()
	}
}

func clearTransaction(tx *sql.Tx, rollbackCallback func(), errCallback func(err error)) {

	err := tx.Rollback()

	if !(err != nil && err == sql.ErrTxDone) {
		rollbackCallback()
	}
	if err != sql.ErrTxDone && err != nil {
		errCallback(err)
	}
}
